Computerized Investing - Spreadsheet Collection/Spreadsheet Collection.iso/pc/ibm11/bondirr.doc

Text File | 1991-07-12 | 16.9 KB | 374 lines

Determining the Yield on Fixed Income Investments
By Fred Shipley
Computerized Investing, November/December 1988

     In the bond program, we dealt with the effects of changing
interest rates on the volatility of bond prices.  This provided a
measure of riskiness.  The other side of the coin is the return
bonds offer.  While the various components of this return are
easy to determine, computing the actual return requires a little
patience.

Finding Information to Determine Returns

     Before we discuss the calculation of bond yields, we should
point out where to find the needed information.  Bond quotes are
reported in the financial press only for U.S. government bonds,
exchange-listed bonds and a small sample of newly issued bonds.
While these quotes are useful and may provide sufficient
information to get started on the valuation process, much is
omitted.  For example, the Wall Street Journal calculates only
the current yield for exchange-listed bonds.  Yields to maturity
are presented only for Treasury bonds and the selected new
issues.  These listings represent a small part of the traded
fixed-income securities.  An investor must look elsewhere to
obtain information on other fixed income securities.

Moody's Investors Services publishes the monthly Moody's Bond
Record and Standard & Poor's publishes the monthly S&P Bond
Guide.  Both of these compact publications provide current yields
and yields to maturity for the bonds they cover.  The Bond Guide,
for example, lists nearly 7,000 separate issues.  These
publications also include the information necessary to accurately
determine bond yields at times other than the time of
publication.  These investor information services also offer
comprehensive company profiles in other publications, which
contain basic information about issued bonds.  Finally, investors
should be able to obtain the same information from their brokers.

While these information services satisfy many investors' needs,
they do not provide the timeliness essential for an active
investor or an investor with a large portfolio.  The bond guides
appear only monthly; many brokers do not have the necessary
information available, or may not understand its implications.
Moreover, from a planning perspective, an investor wants to
determine a reasonable required return and evaluate whether a
prospective bond investment offers that return.  Only by being
able to determine yields will an investor prepare an appropriate
evaluation of bond alternatives.

A Measure of Current Bond Yield

There are several measures of return that are frequently used in
bond analysis.  These range from the simple to the sublime (or at
least the hard to understand).  At the simplest end is what we
call current yield.  The current yield on a bond is just the
annual interest payment divided by the current market price of
the security.

                       Annual Interest
    Current Yield  =  -----------------
                         Market Price

For example, suppose you were interested in IBM's 10.25% bonds
that mature in 1995.  They are listed bonds, traded on the New
York Exchange, and recently closed at 105.  (Bond prices are
quoted as a percentage of face value, so a quote of 105 means
105% of $1,000, or a price of $1,050.)  The current yield is
simply:

                        $102.50
    Current Yield  =  -----------
                       $1,050.00

                   =  0.0976 or 9.76%

While current yield is easy to calculate, and is reported in the
financial press for listed bonds, it is not a good measure of
return for long term fixed income investments.  In particular,
the current yield does not consider the effect of price changes
or the possibility of repayment of principal at maturity.

Yield to Maturity

The most frequently used measure of bond returns over time is the
yield to maturity (YTM).  As the name implies, this yield measure
includes the value of all future cash flows until the bond
matures.  The yield to maturity is simply the interest rate (or
rate of return) that equates the current market price of the bond
to the value of all future cash flows until maturity.
Determining the cash flows is easy, since they are contractually
fixed.  Solving the equation to determine the yield is not so
easy.  We will use the @IRR function in 1-2-3, but it is
important to note how this works.

Lotus 1-2-3's @IRR Function

The term internal rate of return is simply a way of saying the
return that makes the value of future cash flows equal to some
current amount.  1-2-3's @IRR function operates just the way we
would if we were using a calculator to solve the present value
equation.  That is, we would guess a rate of return and use it to
determine the present value of the cash flows.  If the guessed
return gives a present value above the current market price, it
means that our guess was too low.  We must use a higher rate of
return (or discount rate) to get a lower value.  This goes back
to our discussion (in the bond program) of the effects of changes
in interest rates on bond values.  When interest rates go up,
bond values fall.  Conversely, if our guess results in a value
that is below the market price, we must use a lower estimate of
the yield.  In other words, lower interest rates result in higher
bond values.  This iterative process continues until we can make
the value of the future cash flows equal to the bond's current
market price.

The form of the @IRR function is:

  @IRR(GUESS,RANGE)

  Where:  GUESS is an initial estimate of the yield to maturity,
          and

          RANGE is a range of cells that contain the cash flows
          being evaluated.

Implicit in this function is the assumption that the cash flows
occur at regularly spaced time periods.  If we buy a bond at an
interest payment date, this is valid.  If not, we must do the
trial and error process ourselves.

We could simply use the current yield for our initial guess of
the rate, but this might be substantially in error.  In 1-2-3 the
@IRR function makes 20 attempts to solve the equation.  If the
program does not get an answer that is correct to within seven
decimal places in 20 tries, it gives an error (ERR) message.  If
this occurs, you make another guess about the initial rate and
try again.  Usually the error condition is not a problem, but it
may arise if the initial guess is far off the mark.  We will
develop shortly a way of getting a better estimate of the yield
to maturity.

Financial Considerations in Using Yield to Maturity

An investment's yield depends on the frequency of cash flows.  In
the case of most bonds, especially corporate and most Treasury
bonds, these cash flows occur every six months.  Thus the rate
that emerges as the solution is a semiannual return.  The usual
practice is to simply double that rate, and quote that as the
yield to maturity.   If we arrived at a return of 5%, we would
simply double that and say the yield to maturity was 10%.  A more
precise way of stating the return would be 10% compounded
semiannually, or to convert that rate into an effective annually
compounded return of 10.25%.  Nevertheless, 10% is  the way
yields to maturity are quoted.

Another frequently ignored factor is the implicit assumption that
all cash flows (the periodic interest payments) are reinvested
when received at the calculated yield.  The mathematics of
present value require this.   For example, if you decided to take
the interest payments as income then your realized return over
the time to maturity will be less than the yield to maturity you
determined.

In addition, we are presuming that interest rates do not change
over the period to maturity.  If interest rates do change, our
realized rate of return will be different than our estimated
yield to maturity.  If interest rates increase and you continue
to reinvest the interest payments you have received, your
realized return will be greater than the yield to maturity.  You
must remember that the yield is an estimate of future returns --
if you hold the bond to maturity and your market opportunities do
not change.  Changing market conditions and interest rates will
change your returns over time.  For this reason, some people
refer to yield to maturity as a promised yield or expected yield.

Approximating the Yield to Maturity

Estimating the yield to maturity involves approximating the
average annual cash flows and dividing that by an average annual
investment over the period to maturity.  Determining the average
annual cash flows is straightforward.  We simply calculate the
annual amount of interest by multiplying the annual coupon rate
by the face amount.

To estimate the average change in value of the bond until
maturity we subtract the current market value from the face
amount and divide by the number of years to maturity.  This gives
the average annual increase in value if the bond is currently
selling at a discount from face value.  If the bond is selling at
a premium, we get the average annual decrease in value.

It is in the estimation of the price changes that the greatest
degree of approximation occurs.  We are assuming that the price
changes by the same amount each year.  Even if interest rates
remain unchanged, the value of the bond will increase faster, the
closer you get to maturity.  This approximation gives greater
weight to earlier price changes than is actually the case.  When
we calculate the average value of the bond, we will adjust for
this.

The denominator of this equation is simply our average investment
in the bond, or its average value between the time we purchase it
and maturity.  Our first inclination would be to simply add the
current price and face amount together and divide by two.  But,
since the price change is weighted more heavily toward earlier
changes, we will assign a weight greater than 50% to the current
value.  On the basis of a number of simulations, analysts have
discovered that a 60% weight for the current value and a 40%
weight for the face amount is the best approximation.

               cF + (F - P)/T
    AYTM  =  -----------------
              (0.6)P + (0.4)F

    Where:  c is the annual coupon rate,

            F is the face amount of the bond,

            P is the current market price of the bond, and

            T is the number of years to maturity.

Applying this formula to the IBM bond mentioned earlier gives an
approximate yield to maturity of:

              (0.1025)($1,000) + ($1,000 - $1,050)/7
    AYTM  =  ----------------------------------------
                  (0.60)($1,050) + (0.40)($1,000)

               $102.50 - $7.14
          =  -------------------
              $630.00 + $400.00

                $95.36
          =  -----------  =  0.09258  or  9.258%
              $1,030.00

For those of you who are waiting with anticipation, the actual
yield to maturity is 9.264%.  The approximation gives us a very
reasonable guess to use as an input into the @IRR function.  The
only remaining task is to set up a range with the cash flows to
be evaluated.

You may wonder why we should continue if the approximation we
derived is so close to the actual yield.  There are several
reasons.  First, the approximation will vary as the current bond
price varies.  The greater the difference between the current
price and the face amount, the greater the error.  For example, a
deep discount bond selling at 52 ($520.00), paying an 11.25%
coupon and maturing in 20 years has an actual yield to maturity
of 21.95%, yet the approximate yield is only 19.17%.  This is
simply not an acceptable figure with which to plan investment
decisions.

Second, by setting up a table to determine the actual yield to
maturity, we establish numbers for examining changes  in our base
assumptions about current interest rates and possible changes.
Since the yield to maturity is based on an assumption that market
interest rates do not change, an investor will be very interested
in determining the effects of potential changes on the realized
rate of return.

The rest of this article deals with how the Bondirr spreadsheet
was setup to determine the yield to maturity.  The necessary
formulas appear at the end of the article.

Setting Up the Data Inputs

By entering exact dates, we can evaluate yield in between the
regular semiannual interest payment dates.  1-2-3's date functions
take care of tracking the number of days until the next interest
payment.  You can set any format you wish; we use dates that are
formatted in Lotus' first date format, with day, month and year.

                          Figure 1
                        Input Data Items

           A        B              C   D     E     F
1            Data Input Area
2                                  Month    Day  Year
3     Current Date..........           10    11  1988
4     Next Interest Date....           10    15  1988
6     Maturity Date.........           10    15  1995
7
8     Coupon Rate...........         10.25%
9     Market Price..........     $1,050.00
10    Face Value............     $1,000.00
11
12
13    Accrued Interest:             $50.12
14    Approximate YTM:               9.258%
15
16
17    Test YTM.............          9.264%
18    Value of Cash Flows:           $0.00
19    Internal Rate of Return:       8.422%


Because the bond may be purchased between interest payment dates,
we must account for accrued interest.  Since the purchaser of the
bond will receive the full semiannual interest payment on the
next payment date, he or she must pay to the seller the interest
that has accrued since the last payment.  We determine this
amount by multiplying the semiannual coupon by the proportion of
time that passed since the last payment date.  This is calculated
by the formula in cell D13:

    D14:  +$D$8/2*$D$10*((182.625-@DATE($F$4-1900,$D$4,$E$4)+
          @DATE($F$3-1900,$D$3,$E$3))/182.625)

The table for determining the cash flows and their present values
is placed one screen to the right of the data input area.  The
date function takes the date information from the input area and
sets up the column of dates.  The first two entries, in rows 6
and 7, simply use the input data.  The remaining rows use a
formula that adds six months (1/2 of an average year, which is
365.25 days) to the preceding date.  This formula may result in a
case or two of dates that are off by a day; the impact on the
calculations is negligible.

The formulas are copied down 60 rows to create enough space for a
bond with a 30-year maturity.  This will suffice for most bonds.
If you wish to evaluate bonds with a longer maturity, simply copy
the formula down more rows.

Finally, to determine the actual cash flows and their present
values, the following formulas are entered into cells J6 through
K7, respectively.

    J6: -$D$9-$D$13
    K6: +$J$6
    J7: +$D$8*$D$10/2
    K7: +$J7/((1+$D$17/2)^((I7-$I$6)/182.625))

The dollar signs ($) are entered as indicated so that the
original data is referenced when the formulas were copied down
the number of rows until maturity.  Remember that the last
payment includes the repayment of principal, so the last formula
is given below.  Where this formula appears depends on the
maturity of the bond.  For the IBM bond, this is in row 21.

    J20: +$D$8*$D$10/2+$D$10

Determining the Yield to Maturity

To let you see how the process works, enter the number you
determined as the approximate yield to maturity from cell D14
into cell D17.  If the value of the cash flows shown in cell D18
is positive, enter a larger yield.  If the value in cell D18 is
negative, use a smaller number.  Continue in this fashion until
you get a value that is very close to zero.  The actual number
for the yield to maturity in D18 is 0.09264.  It would be
difficult to get much closer to zero than the value shown.  The
formula in cell D18 is simply sum of the present values of the
cash flows.

Be sure that the range of the @SUM function includes your entire
range of discounted cash flows.  We have used the entire 30 year
range, so the formula does not have to be changed every time we
change maturity.  Empty cells do not affect the formula.  You
could also use the @IRR function to give you the answer, as we
have programmed in cell D19.

We used the approximate yield to maturity for our initial guess
of the value.  Since the cash flows are received every six
months, we follow the usual convention for simply multiplying the
rate by two for the annual return.  This formula will give us a
significantly different rate when the purchase date is shortly
before the next interest payment date.  If the purchase is on or
shortly after an interest date, the formula works fairly well.
The reason is simply the assumption that the cash inflows and
outflow occur at equally spaced time intervals.

(c) Copyright 1988 by the
American Association of Individual Investors